﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using _4T.Data;
using System.Transactions;
using System.Globalization;

namespace _4T.DAL
{
    public class PatientInfoDAL
    {
        public PatientInfoDAL() { }

        public int InsertPatientInfo(string patientName, DateTime birthday, string gender, string idNumber, string permAddr,
                                        string tempAddr, string mobile, string phone, string emergency, string representativePhone,
                                        string localContact, string lcpPhone, string localMedicalStaff, string lmsPhone,
                                        string medicalFacility, string note)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "PatientInfoInsert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@PatientName", SqlDbType.NText).Value = patientName;
                    cmd.Parameters.Add("@Birthday", SqlDbType.DateTime).Value = birthday;
                    cmd.Parameters.Add("@Gender", SqlDbType.VarChar, 20).Value = gender;
                    cmd.Parameters.Add("@IDNumber", SqlDbType.VarChar, 20).Value = idNumber;
                    cmd.Parameters.Add("@PermanentAddress", SqlDbType.NText).Value = permAddr;
                    cmd.Parameters.Add("@TemporaryAddress", SqlDbType.NText).Value = tempAddr;
                    cmd.Parameters.Add("@MobileNumber", SqlDbType.VarChar, 50).Value = mobile;
                    cmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar, 50).Value = phone;
                    cmd.Parameters.Add("@EmergencyRepresentative", SqlDbType.NText).Value = emergency;
                    cmd.Parameters.Add("@RepresentativePhoneNumber", SqlDbType.VarChar, 50).Value = representativePhone;
                    cmd.Parameters.Add("@LocalContactPoint", SqlDbType.NText).Value = localContact;
                    cmd.Parameters.Add("@LocalContactPointPhoneNumber", SqlDbType.VarChar, 50).Value = lcpPhone;
                    cmd.Parameters.Add("@LocalMedicalStaff", SqlDbType.NText).Value = localMedicalStaff;
                    cmd.Parameters.Add("@LocalMedicalStaffPhoneNumber", SqlDbType.VarChar, 50).Value = lmsPhone;
                    cmd.Parameters.Add("@MedicalFacility", SqlDbType.NText).Value = medicalFacility;
                    cmd.Parameters.Add("@Note", SqlDbType.NText).Value = note;
                    cmd.Connection = conn;

                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public int UpdatePatientInfo(string patientName, DateTime birthday, string gender, string idNumber, string permAddr,
                                        string tempAddr, string mobile, string phone, string emergency, string representativePhone,
                                        string localContact, string lcpPhone, string localMedicalStaff, string lmsPhone,
                                        string medicalFacility, string note, string profileInfoCode)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "PatientInfoUpdate2";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@PatientName", SqlDbType.NText).Value = patientName;
                    cmd.Parameters.Add("@Birthday", SqlDbType.DateTime).Value = birthday;
                    cmd.Parameters.Add("@Gender", SqlDbType.VarChar, 20).Value = gender;
                    cmd.Parameters.Add("@IDNumber", SqlDbType.VarChar, 20).Value = idNumber;
                    cmd.Parameters.Add("@PermanentAddress", SqlDbType.NText).Value = permAddr;
                    cmd.Parameters.Add("@TemporaryAddress", SqlDbType.NText).Value = tempAddr;
                    cmd.Parameters.Add("@MobileNumber", SqlDbType.VarChar, 50).Value = mobile;
                    cmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar, 50).Value = phone;
                    cmd.Parameters.Add("@EmergencyRepresentative", SqlDbType.NText).Value = emergency;
                    cmd.Parameters.Add("@RepresentativePhoneNumber", SqlDbType.VarChar, 50).Value = representativePhone;
                    cmd.Parameters.Add("@LocalContactPoint", SqlDbType.NText).Value = localContact;
                    cmd.Parameters.Add("@LocalContactPointPhoneNumber", SqlDbType.VarChar, 50).Value = lcpPhone;
                    cmd.Parameters.Add("@LocalMedicalStaff", SqlDbType.NText).Value = localMedicalStaff;
                    cmd.Parameters.Add("@LocalMedicalStaffPhoneNumber", SqlDbType.VarChar, 50).Value = lmsPhone;
                    cmd.Parameters.Add("@MedicalFacility", SqlDbType.NText).Value = medicalFacility;
                    cmd.Parameters.Add("@Note", SqlDbType.NText).Value = note;
                    cmd.Parameters.Add("@ProfileCode", SqlDbType.VarChar, 50).Value = profileInfoCode;
                    cmd.Connection = conn;

                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public PatientInfo GetPatientInfo(string profileCode)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;
                PatientInfo patient = null;
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
                {
                    SqlConnection conn = new SqlConnection(conString);
                    SqlCommand cmd = new SqlCommand();
                    SqlDataReader reader = null;
                    try
                    {
                        conn.Open();
                        cmd.CommandText = "PatientInfoGet";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ProfileInfoCode", SqlDbType.VarChar, 50).Value = profileCode;
                        cmd.Connection = conn;

                        reader = cmd.ExecuteReader();

                        if (reader.HasRows)
                        {
                            reader.Read();
                            patient = GetPatientFromReader(reader);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        if (conn.State != ConnectionState.Closed)
                            conn.Close();
                    }
                    scope.Complete();
                }

                return patient;
            }

            return null;
        }

        public PatientInfo GetPatientInfo2(string profileCode)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;
                PatientInfo patient = null;
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
                {
                    SqlConnection conn = new SqlConnection(conString);
                    SqlCommand cmd = new SqlCommand();
                    SqlDataReader reader = null;
                    try
                    {
                        conn.Open();
                        cmd.CommandText = "PatientInfoGet2";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ProfileCode", SqlDbType.VarChar, 50).Value = profileCode;
                        cmd.Connection = conn;

                        reader = cmd.ExecuteReader();

                        if (reader.HasRows)
                        {
                            reader.Read();
                            patient = GetPatientFromReader(reader);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        if (conn.State != ConnectionState.Closed)
                            conn.Close();
                    }
                    scope.Complete();
                }

                return patient;
            }

            return null;
        }

        private PatientInfo GetPatientFromReader(SqlDataReader reader)
        {
            string patientName = reader.GetString(0);

            DateTime birthday = new DateTime();
            if (reader.GetValue(1) != DBNull.Value)
                birthday = reader.GetDateTime(1);

            string gender = string.Empty;
            if (reader.GetValue(2) != DBNull.Value)
                gender = reader.GetString(2);

            string idNumber = string.Empty;
            if (reader.GetValue(3) != DBNull.Value)
                idNumber = reader.GetString(3);

            string permAddr = string.Empty;
            if (reader.GetValue(4) != DBNull.Value)
                permAddr = reader.GetString(4);

            string tempAddr = string.Empty;
            if (reader.GetValue(5) != DBNull.Value)
                tempAddr = reader.GetString(5);

            string mobile = string.Empty;
            if (reader.GetValue(6) != DBNull.Value)
                mobile = reader.GetString(6);

            string phone = string.Empty;
            if (reader.GetValue(7) != DBNull.Value)
                phone = reader.GetString(7);

            string emergency = string.Empty;
            if (reader.GetValue(8) != DBNull.Value)
                emergency = reader.GetString(8);

            string representativePhone = string.Empty;
            if (reader.GetValue(9) != DBNull.Value)
                representativePhone = reader.GetString(9);

            string contactPoint = string.Empty;
            if (reader.GetValue(10) != DBNull.Value)
                contactPoint = reader.GetString(10);

            string contactPhone = string.Empty;
            if (reader.GetValue(11) != DBNull.Value)
                contactPhone = reader.GetString(11);

            string medicalStaff = string.Empty;
            if (reader.GetValue(12) != DBNull.Value)
                medicalStaff = reader.GetString(12);

            string medicalPhone = string.Empty;
            if (reader.GetValue(13) != DBNull.Value)
                medicalPhone = reader.GetString(13);

            string facility = string.Empty;
            if (reader.GetValue(14) != DBNull.Value)
                facility = reader.GetString(14);

            string note = string.Empty;
            if (reader.GetValue(15) != DBNull.Value)
                note = reader.GetString(15);

            return new PatientInfo(patientName, birthday, gender, idNumber, permAddr, tempAddr, mobile, phone,
                emergency, representativePhone, contactPoint, contactPhone, medicalStaff, medicalPhone, facility, note);
        }
    }
}